# -*- coding: utf-8 -*-
'''
Created on Sep 6, 2016

@author: hw
'''

import tushare as ts
import pandas as pd
from sqlalchemy import create_engine
import MySQLdb
from settings import DATABASES
from sqlalchemy.sql.sqltypes import *
from threading import Thread
from Queue import Queue
from mutex import mutex
import threading 
class StockDict(object):
    def __init__(self, **kwargs):
        self.engine = kwargs.get("engine", "mysql")
        self.db = kwargs.get("db")
        self.user = kwargs.get("user", "root")
        self.password = kwargs.get("passwd", "root")
        self.host = kwargs.get("host", "localhost")
        self.port = kwargs.get("port", 3306) 
        self.charset = kwargs.get("charset", "utf8")
        self.mutex = threading.Lock()
        self.conn = MySQLdb.connect(host=self.host,user=self.user,passwd=self.password,port=self.port,db=self.db,charset=self.charset)
        self.engine = create_engine(self.engine + "://" + self.user + ":" + self.password + "@" + self.host + "/" + self.db + "?charset=" + self.charset)
  
    def saveDictToDb(self):
        df = ts.get_stock_basics()
        df["market"] = ['XSHG' if x.startswith('6') else 'XSHE' for x in df.index]
        dttype = {"code":VARCHAR(32), "market":VARCHAR(32), "name":VARCHAR(32), "industry":VARCHAR(32), "area":VARCHAR(32), "pe":VARCHAR(32)}
        df.to_sql('stock_baseinfo', self.engine, if_exists='replace', dtype=dttype)
        
    def getDict(self):
        sql = "select * from stock_baseinfo " 
        df = pd.read_sql(sql, self.conn,index_col="code")
        print df
        
        
    def saveDayKline(self):
        while ~self.taskQueue.empty():
            stockcode = None
            market = None
            df = None
            if self.mutex.acquire(1):
                stockcode, market = self.taskQueue.get()
                self.mutex.release()
            try:
                print stockcode, market
                df = ts.get_h_data(stockcode, '2016-09-08', '2016-09-09')
                print df
            except:
                continue
            try:
                df["code"] = stockcode + "." + market
            except:
                continue
            print df
            dttype = {"code":VARCHAR(32)}
            df.to_sql('stock_his_daykline', self.engine, if_exists='append', dtype=dttype)
        
    def saveHisDayKline(self):
        con = MySQLdb.connect(host=DATABASES["hqdata"].get("host"), user=DATABASES["hqdata"].get("user"), passwd=DATABASES["hqdata"].get("password"), db=DATABASES["hqdata"].get("db"), charset="utf8")
#         sql = 'select code,market from stock_baseinfo '
        sql = 'select distinct a.code,a.market from stock_baseinfo a left join stock_his_daykline b on concat(a.code,".",a.market) = b.code and b.date = 20160908 where b.code is null '
        codelist = pd.read_sql(sql, con)
        self.taskQueue = Queue(len(codelist))
        for index in codelist.index:
            self.taskQueue.put((codelist.iloc[index].code, codelist.iloc[index].market))
        print codelist
        
        threadpool = []
        num = 8
        for i in range(num):
            thread = Thread(target=self.saveDayKline())
            threadpool.append(thread)
        for i in range(num):
            threadpool[i].start()
        for i in range(num):
            threadpool[i].join()    

         
if __name__ == '__main__':
    print DATABASES["hqdata"]
    dict = StockDict(host=DATABASES["hqdata"].get("host"), user=DATABASES["hqdata"].get("user"), passwd=DATABASES["hqdata"].get("password"), db=DATABASES["hqdata"].get("db"), charset="utf8")
#     dict.saveHisDayKline()
    dict.getDict()
